#--------- Date:2017-6-28 ---------
#Owner: baoshan.zhang
#Summary:Class used to fetch security daily's info

import pymssql
import pandas as pd
import pandas.io.sql as sql
import pandas.io.sql as sql
from pandas import *
import numpy as np
import math
from common.statistic import *
from matplotlib import pyplot as plt

class SecurityInfo:
    def __init__(self,securityId):
        self.conn = pymssql.connect(server='.', user='sa', password='qweqwe', database='JYDB2')
        self.securityId = securityId

    #fetch daily quote info on given fromDt(yyyy-MM-dd) toDt(yyyy-MM-dd) for given securityId(JYDB2's Secmain'SecuCode)
    def __getDailyClosePrice(self,fromDtStr,toDtStr):
        query = 'SELECT M.ChiNameAbbr,Q.TradingDay,Q.ClosePrice,Q.TurnoverVolume,Q.TurnoverValue ' \
              'FROM dbo.QT_DailyQuote Q JOIN dbo.SecuMain M ON Q.InnerCode=M.InnerCode WHERE M.SecuCode= \'%s\' ' \
              'And Q.TradingDay>= \'%s\' And Q.TradingDay<=\'%s\' ORDER  BY Q.TradingDay ' \
                % (self.securityId, fromDtStr,toDtStr)
        df = sql.read_sql(query, self.conn, index_col=['TradingDay'], parse_dates=['TradingDay'])
        return df

    def __getDailyLnReuturnStatistics(self,fromDtStr,toDtStr):
        df = self.__getDailyClosePrice(fromDtStr,toDtStr)
        df = DataFrame(df['ClosePrice'],index=df.index)
        df['ln'] = np.log(df['ClosePrice'])
        df['lnReturn'] = df['ln'] - df['ln'].shift(1)
        return df

    def getVaR(self,fromDtStr,toDtStr,confidence,testNormalitity=False):
        # calculate security's VaR value with Monto Carlo methd

        #Paramsters:
        #=============
        #fromDtStr: string with format(yyyy-MM-dd)
        #toDtStr:   string with format(yyyy-MM-dd)
        #confidence: float
        #            confidence range ; 95%/99% etc
        #testNormalitity: boolean
        #                 if verify the given sample comply with normal distribution

        #Returns:
        #====================
        #value at risk: float

        np.random.seed(2000)
        df = self.__getDailyLnReuturnStatistics(fromDtStr, toDtStr)
        s0 = df.ix[df.index[-1]]['ClosePrice'].item()
        r = df.lnReturn.mean()
        sigma = df.lnReturn.std()
        T=1.0
        M=50
        I=250000
        paths = StatisticUtility.get_paths(s0,r,sigma,T,M,I)

        MC_Price_DF = DataFrame(paths)
        MC_Price_DF_LN_RETURN = np.log(MC_Price_DF / MC_Price_DF.shift(1))
        result = MC_Price_DF_LN_RETURN.sum().sort_values(ascending=False)

        if(testNormalitity==True):
            StatisticUtility.normality_tests(result)
            StatisticUtility.generateQQPlot(result)
        # plt.show()

        return result.quantile(1. - confidence)

